/*
* Script para crear la base de datos de CMSF
*/
USE master
GO
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'DMSF_DB')
	DROP DATABASE DMSF_DB
GO
-- Crear Base de datos
CREATE DATABASE DMSF_DB
GO
USE  DMSF_DB
GO

-- Tabla de Roles
CREATE TABLE Role(
	RoleId				INT PRIMARY KEY IDENTITY,
	Name 				VARCHAR(100) NOT NULL UNIQUE,
	CHECK (Name = 'Administrator' OR Name = 'Normal')
)

INSERT INTO Role Values('Administrator'), ('Normal')

-- Tabla de Usuarios
CREATE TABLE [User](
	UserId				INT PRIMARY KEY IDENTITY,
	RoleId				INT NOT NULL,
	Username			VARCHAR(30) NOT NULL UNIQUE,
	Password			VARCHAR(200) NOT NULL,
	Email				VARCHAR(100) NOT NULL UNIQUE,
	Name				VARCHAR(100),
	ActivationKey		VARCHAR(100),
	CreatedDate			DATETIME,
	ModifiedDate		DATETIME,
	IsActive			BIT,
	ApprovedByAdmin		BIT,
	PrefersRandom		BIT
)

ALTER TABLE [User] ADD CONSTRAINT fk_User_Role FOREIGN KEY (RoleId) REFERENCES Role(RoleId)

GO

INSERT INTO [User] Values(1, 'rubencg', CONVERT(VARCHAR(32), HashBytes('MD5', '123'), 2), 'rubencg88@yahoo.com.mx', 'Ruben Cardenas', '','2014-04-14', '2014-04-14', 1,1,0)

-- Tabla de Orden de usuarios
CREATE TABLE UserOrder(
	UserOrderId			INT PRIMARY KEY IDENTITY,
	UserId				INT NOT NULL,
	OrderNumber			INT NOT NULL
)

ALTER TABLE UserOrder ADD CONSTRAINT fk_UserOrder_User FOREIGN KEY (UserId) REFERENCES [User](UserId)

GO

-- Tabla de Olvido de password
CREATE TABLE UserPassword(
	UserPasswordId		INT PRIMARY KEY IDENTITY,
	UserId				INT NOT NULL,
	PasswordApproval	VARCHAR(200) NOT NULL,
	IsActive			BIT
)

ALTER TABLE UserPassword ADD CONSTRAINT fk_UserPassword_User FOREIGN KEY (UserId) REFERENCES [User](UserId)

GO

-- Tabla de Donas
CREATE TABLE Donut(
	DonutId				INT PRIMARY KEY IDENTITY,
	Name				VARCHAR(100) NOT NULL,
	UniqueName			VARCHAR(100) NOT NULL UNIQUE,
	[Description]			VARCHAR(400) NOT NULL,
	Url					VARCHAR(300) NOT NULL,
	IsActive			BIT NOT NULL
)

GO

-- Tabla de Preferencia de Donas
CREATE TABLE DonutPreference(
	DonutPreferenceId	INT PRIMARY KEY IDENTITY,
	UserId				INT NOT NULL,
	DonutId				INT NOT NULL,
	OrderNUmber			INT NOT NULL
)

ALTER TABLE DonutPreference ADD CONSTRAINT fk_DonutPreference_User FOREIGN KEY (UserId) REFERENCES [User](UserId)
ALTER TABLE DonutPreference ADD CONSTRAINT fk_DonutPreference_Donut FOREIGN KEY (DonutId) REFERENCES [Donut](DonutId)
